 

libname pop "C:\Data\Populations States 2000-2009";
libname pop1 "C:\Data\Populations CA FL NY\By State Age and Sex 1970-1999";


%macro pop_c1(year,state1,state2,state3,state4);
proc means data = pop1.Pop90s noprint;
   var POP POPM POPF;
   output out = junk1 sum = all&year. male&year. female&year.;
   where STATE in ("&state1.","&state2.","&state3.","&state4.") and age in (22,23) and year = &year.;
run;
proc means data = pop1.Pop90s noprint;
   var POP;
   output out = junk2 sum = all_AZ&year.;
   where STATE = "Arizona" and age in (22,23) and year = &year.;
run;
proc means data = pop1.Pop90s noprint;
   var POP;
   output out = junk3 sum = all_CA&year.;
   where STATE = "California" and age in (22,23) and year = &year.;
run;
proc means data = pop1.Pop90s noprint;
   var POP;
   output out = junk4 sum = all_NY&year.;
   where STATE = "New York" and age in (22,23) and year = &year.;
run;
proc means data = pop1.Pop90s noprint;
   var POP;
   output out = junk5 sum = all_TX&year.;
   where STATE = "Texas" and age in (22,23) and year = &year.;
run;
data pop&year. (drop = _type_ _freq_);
   merge junk1 junk2 junk3 junk4 junk5;
run;
proc datasets;
   delete junk1 junk2 junk3 junk4 junk5;
run;
%mend;
%pop_c1(1990,Arizona,California,XXXXXXXX,XXXXX);
%pop_c1(1991,Arizona,California,XXXXXXXX,XXXXX);
%pop_c1(1992,Arizona,California,New York,XXXXX);
%pop_c1(1993,Arizona,California,New York,XXXXX);
%pop_c1(1994,Arizona,California,New York,XXXXX);
%pop_c1(1995,Arizona,California,New York,XXXXX);
%pop_c1(1996,Arizona,California,New York,XXXXX);
%pop_c1(1997,Arizona,California,New York,XXXXX);
%pop_c1(1998,Arizona,California,New York,XXXXX);
%pop_c1(1999,Arizona,California,New York,Texas);

%macro pop_c2(year,state1,state2,state3,state4,state5,state6);
proc means data = pop.Sc_est2009_agesex_res noprint;
   var POPESTIMATE&year.;
   output out = junk1 sum = all&year.;
   where NAME in ("&state1.","&state2.","&state3.","&state4.","&state5.","&state6.") and age in (22,23) and sex = 0;
run;
proc means data = pop.Sc_est2009_agesex_res noprint;
   var POPESTIMATE&year.;
   output out = junk2 sum = male&year.;
   where NAME in ("&state1.","&state2.","&state3.","&state4.","&state5.","&state6.") and age in (22,23) and sex = 1;
run;
proc means data = pop.Sc_est2009_agesex_res noprint;
   var POPESTIMATE&year.;
   output out = junk3 sum = female&year.;
   where NAME in ("&state1.","&state2.","&state3.","&state4.","&state5.","&state6.") and age in (22,23) and sex = 2;
run;
proc means data = pop.Sc_est2009_agesex_res noprint;
   var POPESTIMATE&year.;
   output out = junk4 sum = all_AZ&year.;
   where NAME = "Arizona" and age in (22,23) and sex = 0;
run;
proc means data = pop.Sc_est2009_agesex_res noprint;
   var POPESTIMATE&year.;
   output out = junk5 sum = all_CA&year.;
   where NAME = "California" and age in (22,23) and sex = 0;
run;
proc means data = pop.Sc_est2009_agesex_res noprint;
   var POPESTIMATE&year.;
   output out = junk6 sum = all_IA&year.;
   where NAME = "Iowa" and age in (22,23) and sex = 0;
run;
proc means data = pop.Sc_est2009_agesex_res noprint;
   var POPESTIMATE&year.;
   output out = junk7 sum = all_NY&year.;
   where NAME = "New York" and age in (22,23) and sex = 0;
run;
proc means data = pop.Sc_est2009_agesex_res noprint;
   var POPESTIMATE&year.;
   output out = junk8 sum = all_TX&year.;
   where NAME = "Texas" and age in (22,23) and sex = 0;
run;
proc means data = pop.Sc_est2009_agesex_res noprint;
   var POPESTIMATE&year.;
   output out = junk9 sum = all_WI&year.;
   where NAME = "Wisconsin" and age in (22,23) and sex = 0;
run;
data pop&year. (drop = _type_ _freq_);
   merge junk1 junk2 junk3 junk4 junk5 junk6 junk7 junk8 junk9;
run;
proc datasets;
   delete junk1 junk2 junk3 junk4 junk5 junk6 junk7 junk8 junk9;
run;
%mend;

%pop_c2(2000,Arizona,California,XXXX,New York,Texas,XXXXXXXXX);
%pop_c2(2001,Arizona,California,XXXX,New York,Texas,XXXXXXXXX);
%pop_c2(2002,Arizona,California,XXXX,New York,Texas,XXXXXXXXX);
%pop_c2(2003,Arizona,California,XXXX,New York,Texas,XXXXXXXXX);
%pop_c2(2004,Arizona,California,Iowa,New York,XXXXX,Wisconsin);
%pop_c2(2005,Arizona,California,Iowa,New York,XXXXX,Wisconsin);
%pop_c2(2006,Arizona,California,Iowa,New York,XXXXX,Wisconsin);
%pop_c2(2007,Arizona,XXXXXXXXXX,Iowa,New York,XXXXX,Wisconsin);
%pop_c2(2008,Arizona,XXXXXXXXXX,Iowa,New York,XXXXX,Wisconsin);
%pop_c2(2009,Arizona,XXXXXXXXXX,Iowa,XXXXXXXX,XXXXX,Wisconsin);

%macro junk(e);
data test ;
   format year 8.;
   set %do yr = 1990 %to 1999; pop&yr. (rename =(all&yr.=all male&yr. = male female&yr. = female all_az&yr. = all_az all_NY&yr. = all_NY all_CA&yr. = all_CA all_TX&yr. = all_TX)) %end;
       %do yr = 2000 %to 2009; pop&yr. (rename =(all&yr.=all male&yr. = male female&yr. = female all_az&yr. = all_az all_CA&yr. = all_CA all_IA&yr. = all_IA all_NY&yr. = all_NY all_TX&yr. = all_TX all_WI&yr. = all_WI))%end;;
   year = 1989 + _N_;
run;
%mend;
%junk(o);

data inter.ins_23_pop_combined_inp (keep = merge_v pop_all  pop_female pop_male   pop_AZ pop_CA pop_IA pop_NY pop_TX pop_WI);
   merge pop1990 pop1991 pop1992 pop1993 pop1994 pop1995 pop1996 pop1997 pop1998 pop1999 
         pop2000 pop2001 pop2002 pop2003 pop2004 pop2005 pop2006 pop2007 pop2008 pop2009;
   pop_all = (all1990 + all1991 + all1992 + all1993 + all1994 + all1995 + all1996 + all1997 + all1998 + all1999 + 
                          all2000 + all2001 + all2002 + all2003 + all2004 + all2005 + all2006 + all2007 + all2008 + all2009)/(2*12);
   pop_female = (female1990 + female1991 + female1992 + female1993 + female1994 + female1995 + female1996 + female1997 + female1998 + female1999 + 
                             female2000 + female2001 + female2002 + female2003 + female2004 + female2005 + female2006 + female2007 + female2008 + female2009)/(2*12);
   pop_male = (male1990 + male1991 + male1992 + male1993 + male1994 + male1995 + male1996 + male1997 + male1998 + male1999 + 
                           male2000 + male2001 + male2002 + male2003 + male2004 + male2005 + male2006 + male2007 + male2008 + male2009)/(2*12);

   pop_AZ = (all_AZ1990 + all_AZ1991 + all_AZ1992 + all_AZ1993 + all_AZ1994 + all_AZ1995 + all_AZ1996 + all_AZ1997 + all_AZ1998 + all_AZ1999 + 
             all_AZ2000 + all_AZ2001 + all_AZ2002 + all_AZ2003 + all_AZ2004 + all_AZ2005 + all_AZ2006 + all_AZ2007 + all_AZ2008 + all_AZ2009)/(2*12);

   pop_CA = (all_CA1990 + all_CA1991 + all_CA1992 + all_CA1993 + all_CA1994 + all_CA1995 + all_CA1996 + all_CA1997 + all_CA1998 + all_CA1999 + 
             all_CA2000 + all_CA2001 + all_CA2002 + all_CA2003 + all_CA2004 + all_CA2005 + all_CA2006)/(2*12);
   pop_IA = (all_IA2004 + all_IA2005 + all_IA2006 + all_IA2007 + all_IA2008 + all_IA2009)/(2*12);
   pop_NY = (all_NY1993 + all_NY1994 + all_NY1995 + all_NY1996 + all_NY1997 + all_NY1998 + all_NY1999 + all_NY2000 + all_NY2001 + 
             all_NY2002 + all_NY2003 + all_NY2004 + all_NY2005 + all_NY2006 + all_NY2007 + all_NY2008)/(2*12);
   pop_TX = (all_TX1999 + all_TX2000 + all_TX2001 + all_TX2002 + all_TX2003)/(2*12);
   pop_WI = (all_WI2004 + all_WI2005 + all_WI2006 + all_WI2007 + all_WI2008 + all_WI2009)/(2*12);
   merge_v = 1; 
run;

proc datasets;
   delete test pop1990 pop1991 pop1992 pop1993 pop1994 pop1995 pop1996 pop1997 pop1998 pop1999 
          pop2000 pop2001 pop2002 pop2003 pop2004 pop2005 pop2006 pop2007 pop2008 pop2009;
run;

